﻿using System;
using System.Configuration;
using System.Data;
using System.Globalization;
using DevMaker.SIC.Dao.Business;


namespace WebApplication1 {
    public partial class Reportes : BasePage {

        readonly TurnoBussiness _turnoBussiness = new TurnoBussiness();
        protected void Page_Load(object sender, EventArgs e) {
            var fechaInicio = DateTime.Now.AddDays(-7);
            var fechaTermino = DateTime.Now;
            var dtfi = new DateTimeFormatInfo { ShortDatePattern = "dd/MM/yyyy", DateSeparator = "/" };

            if (!String.IsNullOrEmpty(Request.Form["fecha_inicio"]))
                fechaInicio = Convert.ToDateTime(Request.Form["fecha_inicio"], dtfi);

            if (!String.IsNullOrEmpty(Request.Form["fecha_termino"]))
                fechaTermino = Convert.ToDateTime(Request.Form["fecha_termino"], dtfi);

            AttributesTemplate.Add("fecha_inicio", fechaInicio.ToString("dd/MM/yyyy"));
            AttributesTemplate.Add("fecha_termino", fechaTermino.ToString("dd/MM/yyyy"));

            var mostrarpaso1 = false;
            var mostrarpaso2 = false;
            var mostrarpaso3 = false;

            if (Request.Form["mostrarPaso"] == null || Request.Form["mostrarPaso"] == "1") {
                var usuarioBussines = new UsuarioBussiness();
                var cajeros = usuarioBussines.ObtieneTodosLosCajeros();
                cajeros.RemoveAll(foo => foo.Nombre.Contains("Bloqueado"));
                cajeros.RemoveAll(foo => foo.Nombre.Contains("Staff"));
                cajeros.RemoveAll(foo => foo.Nombre.Contains("Disponible"));
                AttributesTemplate.Add("Cajeros", cajeros);
                mostrarpaso1 = true;
            }

            if (Request.Form["mostrarPaso"] == "2")
                mostrarpaso2 = true;

            if (Request.Form["mostrarPaso"] == "3")
                mostrarpaso3 = true;

            AttributesTemplate.Add("txtbuscar", Request.Form["txtbuscar"]);
            AttributesTemplate.Add("mostrarPaso1", mostrarpaso1);
            AttributesTemplate.Add("mostrarPaso2", mostrarpaso2);
            AttributesTemplate.Add("mostrarPaso3", mostrarpaso3);

            if (mostrarpaso2) {
                //Aqui logica de reporte
                var query = ConfigurationManager.AppSettings["reporte"];
                query = query +
                        String.Format(" and cierre.fecha_negocio between '{0}' and '{1}'", fechaInicio.ToString("yyyyMMdd"), fechaTermino.ToString("yyyyMMdd"));

                if (!String.IsNullOrEmpty(Request.Form["cajero"])) {
                    query = query + String.Format(" and cajero.User_intPIN = {0}", Request.Form["cajero"]);

                }
                var dataset = GenericQueryUtil.ExecuteGenericqueryDataset(query);
                //var datatable = bar.Tables[0];
                //dataset.Tables[0].TableName = String.Format("Reporte de {0} a {1}", fechaInicio, fechaTermino);
                dataset.Tables[0].TableName = "reporte";
                dataset.DataSetName = "Reporte";
                if (dataset.Tables[0].Rows.Count == 0) {
                    AttributesTemplate.Add("mostrar_mensaje", true);
                    AttributesTemplate.Add("mensaje", "No existen Registros");
                    AttributesTemplate["mostrarPaso1"] = true;
                    return;
                }
                foreach (DataColumn c in dataset.Tables[0].Columns) {
                    c.Caption = c.Caption.Replace("_", " ");
                    c.ColumnName = c.ColumnName.Replace("_", " ");
                }

                // Create a workbook set to hold the template workbook and the report workbook.
                SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();

                // Open the template workbook which contains formats, borders and formulas.
                String filename = Server.MapPath("Templates/reporte.xlsx");
                SpreadsheetGear.IWorkbook templateWorkbook = workbookSet.Workbooks.Open(filename);

                // Get the template range from a defined name in the template workbook.
                //SpreadsheetGear.IRange templateRange = templateWorkbook.Names["NFLDivisionFormat"].RefersToRange;
                SpreadsheetGear.IRange templateRange = templateWorkbook.Names["tabla1"].RefersToRange;

                // Get the number of rows and columns in the template range.
                int templateRangeRowCount = templateRange.RowCount;
                int templateRangeColCount = templateRange.ColumnCount;

                // Create a new workbook with one blank worksheet to hold the new Excel Report.
                SpreadsheetGear.IWorkbook reportWorkbook = workbookSet.Workbooks.Add();
                SpreadsheetGear.IWorksheet reportWorksheet = reportWorkbook.Worksheets[0];
                reportWorksheet.WindowInfo.DisplayGridlines = false;
                reportWorksheet.Name = dataset.DataSetName;

                // Start at cell B2
                int row = 1;
                int col = 1;

                // Insert each DataTable from the DataSet...
                foreach (DataTable datatable in dataset.Tables) {
                    // Get the destination range in the report worksheet.
                    SpreadsheetGear.IRange dstRange = reportWorksheet.Cells[row, col,
                        row + templateRangeRowCount - 1, col + templateRangeColCount - 1];

                    // Copy the template range formats and formulas to the report worksheet.
                    templateRange.Copy(dstRange, SpreadsheetGear.PasteType.All,
                        SpreadsheetGear.PasteOperation.None, false, false);

                    if (row == 1) {
                        // Copy the template range column widths to the report worksheet once.
                        templateRange.Copy(dstRange, SpreadsheetGear.PasteType.ColumnWidths,
                            SpreadsheetGear.PasteOperation.None, false, false);
                    }

                    // Use the TableName for the title of the range - this is a merged
                    // cell centered across the top of the destination range.
                    reportWorksheet.Cells[row, col].Formula = String.Format("Reporte de {0} a {1}", fechaInicio, fechaTermino);

                    // Add a defined name for the new destination range. This defined
                    // name will be adjusted by IRange.CopyFromDataTable, allowing us
                    // to skip over the newly inserted range and any summary rows
                    // added by the template.
                    SpreadsheetGear.IName dstRangeName = reportWorkbook.Names.Add(
                        datatable.TableName.Replace(" ", ""), "=" + dstRange.Address);

                    // Insert the DataTable into the worksheet. This will adjust the defined name,
                    // as well as the formats, cell borders and formulas which were copied from
                    // the template workbook.
                    reportWorksheet.Cells[row + 1, col, row + 3, col].CopyFromDataTable(datatable,
                        SpreadsheetGear.Data.SetDataFlags.InsertCells);

                    // Update the row counter to the end of the inserted table
                    SpreadsheetGear.IRange range = dstRangeName.RefersToRange;
                    row = range.Row + range.RowCount + 1;
                }
                // Auto size all worksheet columns which contain data
                reportWorksheet.UsedRange.Columns.AutoFit();
                for (var i = 0; i < reportWorksheet.UsedRange.ColumnCount; i++) {
                    reportWorksheet.Cells[1, i].ColumnWidth *= 1.15;
                }

                // Stream the Excel spreadsheet to the client in a format
                // compatible with Excel 97/2000/XP/2003/2007/2010.
                Response.Clear();
                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", "attachment; filename=report.xls");
                reportWorkbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.Excel8);
                Response.End();
            }

           
        }


        public override Boolean SafePage { get { return false; } }
        public override string TemplateName {
            get { return "Reportes"; }
        }
    }
}
